-- Esto es por si desea droppear la base de datos
USE master
GO
DROP DATABASE BDFinal_Zhong
GO
-- PREGUNTA 1
IF NOT EXISTS( SELECT * FROM master.dbo.sysdatabases  WHERE Name = 'BDFinal_Zhong')
BEGIN
	CREATE DATABASE BDFinal_Zhong
END
GO
USE BDFinal_Zhong
GO
IF NOT EXISTS(SELECT * FROM sys.tables  WHERE Name = 'Persona')
BEGIN
	CREATE TABLE Persona (Codigo VARCHAR(15), Nombre VARCHAR(MAX) NOT NULL, Apellido VARCHAR(MAX) NOT NULL, Telefono VARCHAR(MAX), Direccion VARCHAR(MAX), CI VARCHAR(7) NOT NULL UNIQUE)
END
GO
-- PREGUNTA 2
DECLARE @name VARCHAR(MAX)
		, @lname VARCHAR(MAX)
		, @title VARCHAR(MAX)
		, @id INT
		, @ptype VARCHAR(MAX)
		, @phone VARCHAR(MAX)
		, @address VARCHAR(MAX)
		, @idpersona VARCHAR(15)
		, @idpt2 VARCHAR(9)
		, @ci VARCHAR(7)
DECLARE qPersona CURSOR FOR
	SELECT p.BusinessEntityID, p.FirstName, p.LastName, p.PersonType, p.Title, ISNULL(a.AddressLine1,'') + ISNULL(' ' + a.AddressLine2,'') AS [Address], pp.PhoneNumber AS Phone
		FROM AdventureWorks2012.Person.Person AS p
			JOIN AdventureWorks2012.Person.BusinessEntityAddress AS bea on p.BusinessEntityID = bea.BusinessEntityID
			JOIN AdventureWorks2012.Person.Address AS a ON  bea.AddressID = a.AddressID
			JOIN AdventureWorks2012.Person.PersonPhone AS pp ON p.BusinessEntityID = pp.BusinessEntityID
OPEN qPersona
FETCH NEXT FROM qPersona INTO @id, @name, @lname, @ptype, @title, @address, @phone
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @ci = '0000000'
	SET @ci = LEFT(@ci, 7 - LEN(CAST(@id AS VARCHAR))) + CAST(@id AS VARCHAR)
	SET @idpt2 = '000000000'
	SET @idpt2 = LEFT(@idpt2, 9 - LEN(CAST(@id AS VARCHAR))) + CAST(@id AS VARCHAR)
	SET @idpersona = @ptype + '-' +  LEFT(@lname,1) + LEFT(@name,1) + '-' + @idpt2
	INSERT INTO Persona
		Values(@idpersona, @name, @lname, @phone, @address, @ci)
	SET @ci = @ci + 1
	FETCH NEXT FROM qPersona INTO @id, @name, @lname, @ptype, @title, @address, @phone
END
CLOSE qPersona
DEALLOCATE qPersona 
GO
-- PREGUNTA 3
IF NOT EXISTS(SELECT * FROM sys.tables  WHERE Name = 'Asistencia')
BEGIN
	CREATE TABLE Asistencia (Id INT, Fecha DATETIME, Ingreso BIT, FechaRegular VARCHAR(MAX), ci VARCHAR(7))
END
GO
CREATE SEQUENCE AsistSeq 
	AS INT 
	MINVALUE 1 
	INCREMENT BY 1 
	CYCLE;
GO
-- PREGUNTA 4
CREATE PROCEDURE AutoAsist 
	@ci VARCHAR(7)
AS
	DECLARE @Helper BIT
			, @Helper2 VARCHAR(MAX)
	SELECT @Helper = Ingreso
		   , @Helper = RIGHT(FechaRegular,5)
		FROM Asistencia
		WHERE ci = @ci
			AND Id IN (SELECT MAX(Id) FROM Asistencia WHERE ci = @ci)
	INSERT INTO Asistencia
		VALUES(NEXT VALUE FOR AsistSeq
			   , CURRENT_TIMESTAMP
			   , (SELECT CASE @Helper WHEN CAST(1 AS BIT) THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)END)
			   , (SELECT CASE @Helper2 WHEN '08:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 12:00')
									   WHEN '12:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 14:00')
									   WHEN '14:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 16:00')
									   WHEN '16:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 08:00')
									   ELSE '08:00'
						 END
				 )
			   , @ci
			   )
GO
-- PREGUNTA 5
CREATE TRIGGER TR_NoRegSalida
	ON Asistencia
	FOR INSERT
AS
	DECLARE @ingreso BIT
			, @Helper BIT
			, @helper2 VARCHAR(MAX)
			, @ci VARCHAR(7)
	SELECT @ci = i.ci
		FROM inserted AS i
	SELECT @ingreso = Ingreso
		FROM Asistencia
			WHERE ci = @ci
	SELECT @Helper = Ingreso
		   , @Helper2 = RIGHT(FechaRegular,5)
		FROM Asistencia
		WHERE ci = @ci
			AND Id IN (SELECT MAX(Id) FROM Asistencia WHERE ci = @ci)
	IF(@ingreso = CAST(1 AS BIT))
	BEGIN
		INSERT INTO Asistencia
			VALUES(NEXT VALUE FOR AsistSeq
			   , CURRENT_TIMESTAMP
			   , (SELECT CASE @Helper WHEN CAST(1 AS BIT) THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)END)
			   , (SELECT CASE @Helper2 WHEN '08:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 12:00')
									   WHEN '12:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 14:00')
									   WHEN '14:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 16:00')
									   WHEN '16:00' THEN (CAST(CONVERT(DATE, CURRENT_TIMESTAMP) AS VARCHAR) + ' 08:00')
									   ELSE '08:00'
						 END
				 )
			   , @ci
			)
	END
GO
-- PREGUNTA 6
CREATE PROCEDURE Report
	@ci AS VARCHAR(7)
AS
	IF(@ci IS NULL)
	BEGIN
		SELECT p.Nombre, p.Apellido, p.CI, a.Fecha AS Ingreso, a.FechaRegular, a.Ingreso
			FROM Asistencia AS a
				JOIN Persona AS p ON a.ci = p.CI
	END
	ELSE
	BEGIN
		SELECT p.Nombre, p.Apellido, p.CI, a.Fecha AS Ingreso, a.FechaRegular, a.Ingreso
			FROM Asistencia AS a
				JOIN Persona AS p ON a.ci = p.CI
			WHERE a.ci = @ci
	END
GO